<?php
/**
 * Copyright (c) 2006-2008, Julien PORTALIER
 * http://featherphp.googlecode.com/
 *
 * Licensed under The MIT License
 * Redistributions of files must retain the above copyright notice.
 */

/**
 * Base class for database interfaces.
 */
abstract class db_base
{
	protected $link = null;
	protected $operators;
	protected $ds;
	protected $conf;
	protected $field_quote;
#	protected $func_escape_string = '';
	public    $table_aliases;
	
	protected $debugs = array();
	
	function __construct($conf, $ds=null)
	{
		$this->ds   = $ds;
		$this->conf = $conf;
	}
	
	/**
 * Ignits connection to database.
 */
	abstract function start();
	
	/**
 * Stops connection to database.
 */
	abstract function stop();
	
	/**
	 * Reports an error. Directly if DEBUG, logs it otherwise. 
	 */
	abstract protected function error($sql=null);
	
	/**
	 * Selects a database to use, from now on.
	 *
	 * @param $database string The database to use.
	 */
	abstract function use_database($database=null);
	
	
	/**
	 * Analyzes a table.
	 * 
	 * @return array Table's description.
	 * @param $table String Table to analyze.
	 * 
	 * @todo Extract more informations. For instance Foreign Keys,
	 * which would be useful to automatically generate activerecords ;)
	 */
	abstract function & describe_table($table);
	
	/**
	 * Sets transaction mode:
	 *
	 * @param $com string  'start', 'commit', 'rollback'.
	 */
	function transactions($com)
	{
		switch($com)
		{
			case 'begin':
			case 'start':    return $this->execute('BEGIN ;');
			case 'commit':   return $this->execute('COMMIT ;');
			case 'rollback': return $this->execute('ROLLBACK ;');
		}
	}
	
	/**
	 * Sets a limit (with pagination).
	 * 
	 * @return string SQL ready limiter.
	 * @param $limit int Number of elements to display.
	 * @param $page  int For pagination.
	 */
	abstract function limit($limit, $page=1);
	
	/**
	 * Executes an SQL query for which no return data is awaited
	 * or no processing on the resulset is wanted.
	 * 
	 * Allows for auto escaping:
	 * \code
	 * $db->select("SELECT email FROM members WHERE nickname LIKE ? ;", $_GET['name'].'%');
	 * $db->select("SELECT email FROM members WHERE nickname LIKE :name ;", array('name' => $_GET['name'].'%'));
	 * \endcode
	 * 
	 * @return MySQL resultset.
	 * @param  $sql String Valid SQL query.
	 */
	abstract function execute($sql);
	
	/**
	 * Parses conditions.
	 * 
	 * Examples:
	 * 
	 * \code
	 * # unsafe:
	 * $db->conditions("id = '$id' AND title LIKE '$title%'");
	 * 
	 * # safe:
	 * $db->conditions(array("id = ? AND title LIKE ?", $id, "$title%"));
	 * $db->conditions(array("created BETWEEN :from AND :to", 'from' => $from, 'to' => "$to"));
	 * 
	 * # safe & simple:
	 * $db->conditions(array('id' => $id, 'title' => "LIKE $title%"));
	 * \endcode
	 * 
	 * @return String
	 * @param $conditions Mixed
	 */
	function conditions($conditions)
	{
		if (is_array($conditions))
		{
			if (is_hash($conditions))
			{
				# safe & simple
				$_conditions = array();
				foreach($conditions as $f => $v)
					$_conditions[] = $this->condition($f, $v);
				$conditions = implode(' AND ', $_conditions);
			}
			else
			{
				# safe (using ':field')
				$conditions = $this->parse_sql_with_parameters($conditions);
			}
		}
		return $conditions;
	}
	
	/**
	 * Parses an SQL query and safely replaces parameters.
	 * 
	 * Examples:
	 * 
	 * \code
	 * parse_sql_with_parameters("SELECT * FROM table WHERE id = ? AND title LIKE ?", $id, "$title%");
	 * parse_sql_with_parameters(array("id = ? AND title LIKE ?", $id, "$title%"));
	 * parse_sql_with_parameters("id = :id AND title LIKE :title", array('id' => $id, 'title' => "$title%"));
	 * parse_sql_with_parameters(array("SELECT * FROM table WHERE id = :id AND title LIKE :title", 'id' => $id, 'title' => "$title%"));
	 * \endcode
	 * 
	 * @param $sql String Full SQL query or just a snippet (eg: WHERE or HAVING part).
	 */
	protected function parse_sql_with_parameters($sql)
	{
	 	$args = is_array($sql) ? $sql : func_get_args();
		$sql  = array_shift($args);
		
		if (is_hash($args))
		{
			# safe with ':field'
			$keys   = array();
			$values = array();
			foreach($args as $k => $v)
			{
				$keys[]   = ":$k";
				$values[] = $this->value($v);
			}
			$sql = str_replace($keys, $values, $sql);
		}
		else
		{
			# safe with '?'
			$str = strtok($sql, '?');
			$sql = '';
			while($str !== false)
			{
				$sql .= empty($args) ? $str : $str.$this->value(array_shift($args));
				$str = strtok('?');
			}
			$sql .= $str;
		}
		
		return $sql;
	}
	
	/**
	 * Parses a single condition.
	 * 
	 * @param $f String
	 * @param $v String
	 * @param $not String
	 */
	function condition($f, $v, $not='')
	{
		$_f = $this->field($f);
		
		# [NOT] IN
		if (is_array($v))
		{
			$str = array();
			
			foreach($v as $k => $vv)
			{
				if (strtoupper($k) != 'NOT')
				{
					if (!is_array($vv))
					{
						$v[$k] = $this->value($vv);
					}
					else
					{
						# IN
						array_push($str, $this->condition($f, $vv));
						unset($v[$k]);
					}
				}
				else
				{
					# NOT IN
					array_push($str, $this->condition($f, $vv, 'NOT'));
					unset($v[$k]);
				}
			}
			
			if (empty($str))
			{
				$str = '';
			}
			else
			{
				$str = implode(" AND ", $str);
				if (empty($v))
				{
					return $str;
				}
				$str .= " AND ";
			}
			
			return "$str$_f $not IN ( ".implode(', ', $v)." )";
		}
		
		# don't process value?
		if (strpos($v, '-! ') === 0)
		{
			$v = str_replace('-! ', '', $v);
			return "$_f $not $v";
		}
		
		# specified (or not) operator
		if (!preg_match("/^({$this->operators}) .+/i", $v))
		{
			$oper = '=';
		}
		elseif (preg_match("/BETWEEN\s+(.+?)\s+AND\s+(.+?)$/i", $v, $m))
		{
			$v1 = $this->value($m[1]);
			$v2 = $this->value($m[2]);
			return "$_f $not BETWEEN $v1 AND $v2";
		}
		else
		{
			list($oper, $v) = explode(' ', $v, 2);
		}
		$v = $this->value($v);
		return "$_f $not $oper $v";
	}
	
	/**
	 * Parses, quotes and secures a single field.
	 * 
	 * @return String
	 * @param  $fields Mixed Collection of fields.  
	 */
	function fields($fields)
	{
		if (!is_array($fields))
		{
			if (strpos($fields, '-! ') === 0) {
				return substr($fields, 3);
			}
			$fields = explode(',', $fields);
		}
		$_f = array();
		foreach ($fields as $f) {
			array_push($_f, $this->field($f));
		}
		return implode(', ', $_f);
	}
	
	/**
	 * Parses, quotes and secures a single field.
	 * 
	 * @return String
	 * @param $f String
	 */
	function field($f)
	{
		$f = trim($f);
		
		# do not process field?
		if (strpos($f, '-! ') === 0) {
			return str_replace('-! ', '', $f);
		}
		
		$q = $this->field_quote;
		
		# function
		if (preg_match('/COUNT\((.+)\)/', $f, $match))
		{
			$f    = $match[1];
			$func = 'COUNT';
		}
		
		# operator
		if (stripos($f, 'DISTINCT') !== false)
		{
			$op = 'DISTINCT';
			$f  = str_ireplace('DISTINCT', '', $f);
		}
		else {
			$op = '';
		}
		$f = trim($f);
		
		if (strpos($f, '.'))
		{
			# model.field type
			list($m, $f) = explode('.', $f);
			$f = ($f != '*') ? "$q$m$q.$q$f$q" : "$q$m$q.$f";
		}
		else
		{
			# simple field
			$f = ($f != '*') ? "$q$f$q" : $f;
		}
		
		return isset($func) ? $func.'('.$f.')' : "$op $f";
	}
	
	/**
	 * Quotes and secures a list of values.
	 * 
	 * @return String
	 * @param $values Array  
	 */
	function values(array $values)
	{
		$arr = array();
		foreach($values as $k => $v) {
			$arr[$k] = $this->value($v);
		}
		return implode(', ', $arr);
	}
	
	/**
	 * Quotes a single value, depending on it's type.
	 */
	function value($v)
	{
		if (!$this->link)
			$this->start();
		
		if (strpos($v, '-! ') === 0)
		{
			# do not process value
			return str_replace('-! ', '', $v);
		}
		elseif ($v === true) {
			return 'TRUE';
		}
		elseif ($v === false) {
			return 'FALSE';
		}
		elseif (empty($v) and strlen($v) == 0) {
			return 'NULL';
		}
		else
		{
#			$func_escape_string = $this->func_escape_string;
##			$v = str_replace(CR.LF, LF, $v);
#			$v = $func_escape_string($v, $this->link);
			$v = $this->escape($v);
			return "'$v'";
		}
	}
	
	/**
	 * Quotes an ORDER BY request.
	 * 
	 * @param $by   String
	 * @param $type String ORDER or GROUP.
	 */
	function order($by, $type='ORDER')
	{
		if (strpos($by, '-! ') === 0) {
			return substr($by, 3);
		}
		elseif (strpos($by, 'RAND()') === false)
		{
			$by = preg_replace_callback('/([\w.]+)\s*(ASC|DESC|NULLS FIRST|NULLS LAST)[\s,]*/',
				array($this, 'preg_order'), $by);
		}
		return "$type BY $by";
	}
	
	/**
	 * Quotes a GROUP BY request.
	 * 
	 * @return String
	 * @param $by String
	 * @param $having Mixed[Optional]
	 */
	function group($by, $having=null)
	{
		$group  = $this->order($by, 'GROUP');
		$having = ($having === null) ? '' : ' '.$this->conditions($having);
		return $group.$having;
	}
	
	/**
	 * SELECTS many rows.
	 * 
	 * \code
	 * $rows = $db->select("SELECT * FROM table ;");
	 * $rows = $db->select("SELECT * FROM table WHERE status = ? ;", $status);
	 * 
	 * $tags = $db->select("SELECT tag FROM tags ;", DBO::PROCESS_LIST);
	 *   -> array('tag1', 'tag2', ...);
	 * 
	 * $tags = $db->select("SELECT id, tag FROM tags ;", DBO::PROCESS_ASSOC_LIST);
	 *   -> array('id1' => 'tag1', 'id2' => 'tag2', ...);
	 * \endcode
	 * 
	 * @return Array   Processed data.
	 * @param $sql     String SQL query.
	 * @param $process String How to process data.
	 */
	function & select($sql=null, $process=null)
	{
		$nb_args = func_num_args();
		
		# get SQL & specified process
		$args    = func_get_args();
		
		$process = ($nb_args > 1 and is_string($args[$nb_args - 1]) and strpos($args[$nb_args - 1], 'PROCESS_') === 0) ?
			array_pop($args) : DBO::PROCESS_FULL;
		
		# query + parse
		$rs = call_user_func_array(array($this, 'execute'), &$args);
		if ($rs)
		{
			$rs = $this->parse_query($rs, $process);
		}
		
		return $rs;
	}
	
    /**
     * Generates a SQL SELECT query.
     * 
     * @return String
     * @param $options Array
     */
    function generate_sql(array &$options)
    {
        $from   = "";
        $fields = "*";
        $where  = null;
        $group  = null;
        $order  = null;
        $limit  = null;
        $joins  = "";
        
        foreach($options as $k => $v)
        {
			switch($k)
			{
				case 'conditions': $where  = 'WHERE '.$this->conditions($v); break;
				case 'fields':     $fields = $this->fields($v, true); break;
				case 'group':      $group  = $this->group($v);  break;
				case 'order':      $order  = $this->order($v);  break;
				case 'from':
					$from = $this->field($v);
					if (isset($options['from_as']))
					{
						$from .= ' AS '.$this->field($options['from_as']);
						unset($options['from_as']);
					}
				break;
				
				case 'joins':
					if (!isset($v[0]))
						$v = array($v);
					
					foreach($v as $j)
					{
						$type   = isset($j['type']) ? $j['type'] : 'INNER';
						$table  = $this->field($j['table']);
						$as     = isset($j['as']) ? 'AS '.$this->field($j['as']) : '';
						
						if (is_array($j['on']))
						{
							foreach($j['on'] as $l => $r) {
								$j['on'][$l] = $this->field($l).' = '.$this->field($r);
							}
							$j['on'] = implode(' AND ', $j['on']);
						}
						$joins .= "$type JOIN $table $as ON {$j['on']} ";
					}
					break;
				
				case 'limit':
					$limit = $this->limit($v, isset($options['page']) ? $options['page'] : null);
					unset($options['page']);
					break;
				
				case 'table_aliases':
					$this->table_aliases = $options['table_aliases'];
					break;
				
				default:
					if ($k != 'page' and DEBUG) {
						trigger_error("Unknown parameter for SELECT: $k", E_USER_WARNING);
					}
			}
		}
		
		return "SELECT $fields FROM $from $joins $where $group $order $limit;";
	}
	
	/**
	 * Parses data from an SQL query.
	 * 
	 * Allows for auto escaping:
	 *   $db->select("SELECT email FROM members WHERE id = ?", $_GET['id']);
	 * 
	 * Data is returned in 3 possible forms, depending on $process
	 * 
	 * DBO::PROCESS_FULL:
	 *   Useful when calling data from multiple tables at once.
	 *   Format: $data[][table][field] = values
	 * 
	 * DBO::PROCESS_LITE:
	 *   Useful when calling data from a single table.
	 *   Format: $data[][field] = values
	 * 
	 * DBO::PROCESS_VALUE:
	 *   No processing is done. Returns a single value.
	 *   Format: $data = value
	 * 
	 * DBO::PROCESS_LIST:
	 *   Lists a single value (eg: a list of tags).
	 *   Format: $data = array(value, value, value, ...)
	 * 
	 * DBO::PROCESS_ASSOC_LIST:  
	 *   Lists a pair of values (eg: useful for HTML's select).
	 *   Format: $data = array(key1 => value1, key2 => value2, ...)
	 * 
	 * DBO::PROCESS_NONE:
	 *   No processing is done.
	 *   Format: $data[] = values (row only)
	 * 
	 * @return Array             Processed resultset.
	 * @param  $rs MySQLResource A valid MySQL resource.
	 * @param  $process String   How to process data.
	 */
	abstract protected function & parse_query($rs, $process=DBO::PROCESS_FULL);
	
	protected function preg_order($m)
	{
		return str_replace($m[1], $this->field($m[1]), $m[0]);
	}
	
	/**
	 * INSERTS a row.
	 * 
	 * \code
	 * $values  = array('title' => $title, 'body' => $body);
	 * $success = $db->insert('table', $values);
	 * \endcode
	 * 
	 * @return Boolean Success or last inserted ID (id $key != null).
	 * @param  $table  String Name of table. 
	 * @param  $data   Array  Hash of fields => values to insert.
	 * @param  $key    String
	 */
	function insert($table, array &$data, $key=null)
	{
		$table  = $this->field($table);
		$fields = $this->fields(array_keys($data));
		$values = $this->values(array_values($data));
		return $this->execute("INSERT INTO $table ( $fields ) VALUES ( $values ) ;");
	}
	
	/**
	 * REPLACES a row.
	 * 
	 * \code
	 * $values  = array('title' => $title, 'body' => $body);
	 * $success = $db->replace('table', $values);
	 * \endcode
	 * 
	 * @return Boolean Success or last inserted ID (id $key != null).
	 * @param  $table  String Name of table. 
	 * @param  $data   Array  Hash of fields => values to replace.
	 */
	function replace($table, array &$data)
	{
		$table  = $this->field($table);
		$fields = $this->fields(array_keys($data));
		$values = $this->values(array_values($data));
		return $this->execute("REPLACE INTO $table ( $fields ) VALUES ( $values ) ;");
	}
	
	/**
	 * INSERTS many rows.
	 * 
	 * \code
	 * $fields  = 'title, body';
	 * $values  = array(
	 *   array($title1, $body1),
	 *   array($title2, $body2),
	 *   array($title3, $body3)
	 * );
	 * $success = $db->insert_many('table', $fields, $values);
	 * \endcode
	 * 
	 * @return Boolean Success?
	 * @param $table  String Name of table.
	 * @param $fields Mixed  List of fields.
	 * @param $values Array  Array of values to insert.
	 */
	function insert_many($table, $fields, array &$values)
	{
		$table   = $this->field($table);
		$_fields = $this->fields($fields);
		$_values = $this->__parse_many_values($values);
		return $this->execute("INSERT INTO $table ( $_fields ) VALUES $_values ;");
	}
	
	/**
	 * REPLACES many rows.
	 * 
	 * \code
	 * $fields  = 'title, body';
	 * $values  = array(
	 *   array($title1, $body1),
	 *   array($title2, $body2),
	 *   array($title3, $body3)
	 * );
	 * $success = $db->replace_many('table', $fields, $values);
	 * \endcode
	 * 
	 * @return Boolean Success?
	 * @param $table  String Name of table. 
	 * @param $fields Mixed  List of fields.
	 * @param $values Array  Array of values to replace.
	 */
	function replace_many($table, $fields, array &$values)
	{
		$table   = $this->field($table);
		$_fields = $this->fields($fields);
		$_values = $this->__parse_many_values($values);
		return $this->execute("REPLACE INTO $table ( $_fields ) VALUES $_values ;");
	}
	
	/**
	 * UPDATES one to many row.
	 * 
	 * \code
	 * $where   = array('id' => $id);
	 * $values  = array('title' => $title, 'body' => $body);
	 * $success = $db->update('table', $where, $values);
	 * \endcode
	 * 
	 * @return Boolean Success?
	 * @param $table String Name of table. 
	 * @param $where Mixed  Conditions.
	 * @param $data Array  Array of values to update.
	 * @param $options Array[optional] Supports 'order', 'page', and 'limit'.
	 */
	function update($table, $where=null, array &$data, array $options=null)
	{
		# query
		$table = $this->field($table);
		if (!empty($where)) {
			$where = 'WHERE '.$this->conditions($where);
		}
		$order = isset($options['order']) ? $this->order($options['order']) : '';
		$limit = isset($options['limit']) ? $this->limit($options['limit'], isset($options['page']) ? $options['page'] : null) : '';
		
		# data
		$values = array();
		foreach($data as $f => $v) {
			$values[] = $this->field($f).' = '.$this->value($v);
		}
		$values = implode(', ', $values);
		
		# query
		return $this->execute("UPDATE $table SET $values $where $order $limit ;");
	}
	
	/**
	 * DELETES one to many rows.
	 * 
	 * \code
	 * # deletes all
	 * $db->delete('users');
	 * 
	 * # deleted one record
	 * $where   = array('id' => $id);
	 * $success = $db->delete('users', $where);
	 * \endcode
	 * 
	 * @return Boolean True on success
	 * @param $table String
	 * @param $where Mixed
	 * @param $options Array[optional]
	 */
	function delete($table, $where=null, array $options=null)
	{
		if (!empty($where))
		{
			$where = 'WHERE '.$this->conditions($where);
		}
		$table = $this->field($table);
		$order = isset($options['order']) ? $this->order($options['order']) : '';
		$limit = isset($options['limit']) ? $this->limit($options['limit'], isset($options['page']) ? $options['page'] : null) : '';
		
		return $this->execute("DELETE FROM $table $where $order $limit ;");
	}
	
	/**
	 * Transforms an array of multiple values.
	 * Used by self::replace_many() and self::insert_many().
	 * 
	 * @return String  Parsed values.
	 * @param  $values Array Values to parse.
	 */
	function __parse_many_values(array &$values)
	{
		$_values = array();
		foreach($values as $i => $d) {
			$_values[$i] = ' ( '.$this->values($d).' ) ';
		}
		return implode(', ', $_values);
	}
	
	/**
	 * Displays list of queries at the end of the page (when in SQL DEBUG mode).
	 */
	function __destruct()
	{
		$this->stop();
		
		# no debug?
		if (DEBUG < 2 or (defined('DB_NO_DEBUG') and DB_NO_DEBUG))
		{
			return;
		}
		
		if (isset($_SERVER['SERVER_ADDR']))
		{
			echo '<table class="DBDebug" summary="SQL queries">' .
				'<tr>' .
				'<th>['.$this->conf['driver'].': '.$this->ds.'] queries ( '.count($this->debugs).' )</th>' .
				'<th>error</th>' .
				'<th>affected</th>' .
				'<th>time</th>' .
				'</tr>';
			
			foreach($this->debugs as $d)
			{
				printf("\n<tr>" .
					"<td>%s</td>" .
					"<td>%s</td>" .
					"<td>%d</td>" .
					"<td>%.02fms</td>" .
					"</tr>",
					$d['sql'], $d['error'], $d['affected'], ($d['time'] * 1000));
			}
			echo '</table>';
		}
		else
		{
			echo LF.LF."DEBUG [".$this->conf['driver'].': '.$this->ds."]".LF;
			
			foreach($this->debugs as $d)
			{
				echo LF.$d['sql'].LF;
				echo "error: ".$d['error'].", ";
				echo "affected: ".$d['affected'].", ";
				printf("time: %.02fms", $d['time'] * 1000);
				echo LF;
			}
			echo LF;
		}
	}
}
?>